﻿CREATE TRIGGER [tr_TrackerRequest_DealerID]
ON [dbo].[t_TrackerRequest] 
FOR UPDATE
AS

IF @@ROWCOUNT = 0 RETURN

IF NOT UPDATE (DealerID) RETURN

DECLARE @LoanID int,@VIN varchar(17),@DealerName sysname,@ShowLoan nvarchar(1024)
SELECT @LoanID = ci.LoanID, @VIN = d.VIN, @DealerName = ci.Dealer FROM v_CarInfo ci
INNER JOIN deleted d ON ci.DealerID = d.DealerID AND ci.VIN = d.VIN
INNER JOIN inserted i ON i.DealerID <> d.DealerID
INNER JOIN v_TrackerRequest tr ON tr.TrackerRequestID = d.ID AND tr.IsActive = 1

IF @LoanID IS NULL RETURN

ROLLBACK TRAN
SET @ShowLoan = dbo.ShowLoan(@LoanID)
RAISERROR('Unable to change dealer on this request because
loan [%d] exists with the same VIN [%s] and Dealer [%s].%s',16,1,@LoanID,@VIN,@DealerName,@ShowLoan)


